import EditExcel,ReadExcel
import os,time
from copy import deepcopy

#初始化excel文件
Src_Path=(os.path.dirname(os.path.abspath(__file__))).replace("\\","/") #文件所处目录路径
ReportFileTem_Path= "D://性能测试//Java公文性能测试 - 报告模板//Java公文性能测试报告-模板.xlsx"

def Get_JmeterData(file_path):

    RE=ReadExcel.Excel(file_path)
    JmeterData_Dict={}
    JmeterData_List=[]

    position_事务数=RE.get_cell_position("test", "事务数")
    position_平均响应时间=RE.get_cell_position("test", "平均响应时间")
    position_异常率=RE.get_cell_position("test", "异常率")
    position_TPS=RE.get_cell_position("test", "TPS")
    position_标题=RE.get_cell_position("test", "标题")

    total_rows=RE.get_rows_count("test")

    for num in range(2,total_rows+1):
        JmeterData_Dict.update(标题=RE.get_cell_value("test",(num,position_标题[1])),
                                事务数=RE.get_cell_value("test",(num,position_事务数[1])),
                                平均响应时间=RE.get_cell_value("test",(num,position_平均响应时间[1])),
                                通过率=RE.get_cell_value("test",(num,position_异常率[1])),
                                TPS=RE.get_cell_value("test",(num,position_TPS[1])))

        JmeterData_List.append(deepcopy(JmeterData_Dict))
        # print(JmeterData_Dict)

    return JmeterData_List

def Get_Image_List(image_type):
    File_Path="D://性能测试//Java公文性能测试 - 测试环境脚本//性能测试截图//"
    folder_path = File_Path
    file_names = []
    file_names_dict={}
    O_list=[]
    H_list=[]
    R_list=[]
    A_list=[]
    T_list=[]
    subfolder_names = [folder for folder in os.listdir(folder_path) if os.path.isdir(os.path.join(folder_path, folder))]

    for subfolder in subfolder_names:
        for file_name in os.listdir(folder_path+"/"+subfolder+"/"):
            subfolder_split=(str(subfolder).split("-"))[1]
            file_names_dict.update(Title=subfolder_split,Image_Name=folder_path+subfolder+"/"+file_name)
            file_names.append(deepcopy(file_names_dict))
    
    for file_name in file_names:
        if "O" in file_name['Image_Name']:
            O_list.append(file_name)
        elif "H" in file_name['Image_Name']:
            H_list.append(file_name)
        elif "R" in file_name['Image_Name']:
            R_list.append(file_name)
        elif "A" in file_name['Image_Name']:
            A_list.append(file_name)
        elif "T" in file_name['Image_Name']:
            T_list.append(file_name)
    
    if image_type=="O":
        return O_list
    elif image_type=="H":
        return H_list
    elif image_type=="R":
        return R_list
    elif image_type=="A":
        return A_list
    elif image_type=="T":
        return T_list
    return O_list,H_list,R_list,A_list,H_list

#聚合报告数据
JmeterData_List=Get_JmeterData("D://性能测试//Java公文性能测试 - 测试环境报告//聚合报告数据.xlsx")

class Report1_Process:
    def __init__(self,ReportFileTem_Path):

        self.EE=EditExcel.Excel(ReportFileTem_Path)
    #表一数据处理
    def Sheet_1_Process(self):
        print("正在处理表一数据！")
        starttime=time.time()
        '''处理 性能测试报告（单场景） 表的数据'''

        #获取 通过率、事务数、平均响应时间、TPS 四列的位置
        column_通过率 = self.EE.check_Cell_Position("性能测试报告（单场景）", "通过率(%)")[1]
        column_事务数 = self.EE.check_Cell_Position("性能测试报告（单场景）", "事务数")[1]
        column_平均响应时间 = self.EE.check_Cell_Position("性能测试报告（单场景）", "平均响应时间(s)")[1]
        column_TPS = self.EE.check_Cell_Position("性能测试报告（单场景）", "TPS")[1]
        
        for JmeterData in JmeterData_List:

            cell=self.EE.check_Cell_Position("性能测试报告（单场景）", JmeterData['标题'])
            
            self.EE.add_Str_to_Cell(sheet_name="性能测试报告（单场景）",row=cell[0],
                                col=[column_通过率,column_事务数,column_平均响应时间,column_TPS],
                                value= [JmeterData['通过率'],JmeterData['事务数'],JmeterData['平均响应时间'],JmeterData['TPS']])
    

        print("工作表：性能测试报告（单场景），数据导入完成！")
        endtime=time.time()
        print("处理时间：",endtime-starttime,"秒")
    #表二数据处理
    def Sheet_2_Process(self):
        print("正在处理表二数据！")
        start_time = time.time()

        Image_List = Get_Image_List("O")#获取图片路径

        sheet_Name = "服务器性能指标（单场景)"
        #添加表二到excel文件
        self.EE.add_Sheet_to_Excel(sheet_name=sheet_Name,col_values=["归属","场景","指标","图标"],cols=[[4,100]],rows=[])

        for Image in Image_List:
            try:
                row=Image_List.index(Image)
                self.EE.add_Str_to_Cell(sheet_name=sheet_Name, row=row+2, col=[1,2,3], value=[Image['Title'],"服务器性能指标","CPU/内存"])
                self.EE.add_Img_to_Excel(sheet_name=sheet_Name, 
                                            row=self.EE.check_Cell_Position(sheet_Name,Image['Title'])[0], 
                                            col=4, 
                                            img_path=Image['Image_Name'])
            except Exception as e:
                print(e)

        end_time = time.time()
        run_time = end_time - start_time
        print("表二数据处理用时为：%s秒" % run_time)

    def Sheet_3_Process(self):
        print("正在处理表三数据！")
        start_time = time.time()
        img_list=Get_Image_List("A")
        '''处理表：聚合性能指标（单场景）'''

        sheet_Name = "聚合性能指标（单场景）"
        self.EE.add_Sheet_to_Excel(sheet_Name,col_values=["归属","场景","指标","图标"],cols=[[4,100]],rows=[])

        for img in img_list:
            row = img_list.index(img)
            self.EE.add_Str_to_Cell(sheet_Name, row=[row*4+2,row*4+3,row*4+4,row*4+5], col=1, value="单场景")
            self.EE.add_Str_to_Cell(sheet_Name, row=[row*4+2,row*4+3,row*4+4,row*4+5], col=2, value=img['Title'])
            self.EE.add_Str_to_Cell(sheet_Name, row=[row*4+2,row*4+3,row*4+4,row*4+5], col=3, value=["Aggregate Report","Hits per Second","Response Times Over Time","Transactions per Second"])
            self.EE.add_Img_to_Excel(sheet_Name, row=row*4+2, col=4, img_path=Get_Image_List("A")[row]['Image_Name'])
            self.EE.add_Img_to_Excel(sheet_Name, row=row*4+3, col=4, img_path=Get_Image_List("H")[row]['Image_Name'])
            self.EE.add_Img_to_Excel(sheet_Name, row=row*4+4, col=4, img_path=Get_Image_List("R")[row]['Image_Name'])
            self.EE.add_Img_to_Excel(sheet_Name, row=row*4+5, col=4, img_path=Get_Image_List("T")[row]['Image_Name'])
                
        end_time = time.time()
        run_time = end_time - start_time
        print("表三数据处理用时为：%s秒" % run_time)

RP=Report1_Process(ReportFileTem_Path)

RP.Sheet_1_Process()

RP.Sheet_2_Process()

RP.Sheet_3_Process()




    


